﻿using System;
using System.Collections.Generic;
using Browis.Package;
using System.IO;
using System.Data;
using log4net;

using System.Data.Common;
using System.Diagnostics;
using Microsoft.Win32;
using System.Configuration;
using GBase.Data.GBaseClient;
using System.Data.SqlClient;

namespace Browis.Cds.Plugin
{
    /// <summary>
    /// 执行包命令
    /// </summary>
    public class DataCommand
    {
        #region parameters 

        /// <summary>
        /// Log
        /// </summary>
        private ILog log = null;

        private IList<ParameterInfo> _parameters;

        /// <summary>
        /// ProviderName
        /// </summary>
        private string _providerName;

        #endregion

        #region Parameters

        public string Text
        {
            get;
            set;
        }

        public string ReserveText
        {
            get;
            set;
        }

        public IList<ParameterInfo> Parameters
        {
            get {
                if (_parameters == null)
                {
                    _parameters = new List<ParameterInfo>();
                }
                return _parameters;
            }
            set {
                _parameters = value;
            }
        }

        private IList<CommandValue> _commandValues;

        public IList<CommandValue> CommandValues
        {
            get
            {
                if (_commandValues == null)
                {
                    _commandValues = new List<CommandValue>();
                }
                return _commandValues;
            }
            set
            {
                _commandValues = value;
            }
        }

        private Stream _dataReader;

        public Stream DataReader
        {
            get { return _dataReader; }
            set { _dataReader = value; }
        }

        /// <summary>
        /// ProviderName
        /// </summary>
        public string ProviderName
        {
            get { return _providerName; }
            set { _providerName = value; }
        }

        #endregion

        #region Constructor

        /// <summary>
        /// Create an instance of <see cref="DataCommand"/>.
        /// </summary>
        public DataCommand()
        {
            log = LogManager.GetLogger("UnPack");
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Execute command
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public long Execute(IDbConnection conn, IDbTransaction tran, string pakCommandName, string pakDBName)
        {
            long effectedCount = 0;
            long nMGEffectedCount = 0;

            IDbCommand cmd = conn.CreateCommand();

            if (tran != null)
            {
                cmd.Transaction = tran;
            }

            //cmd.CommandType = System.Data.CommandType.Text;
            GetCommentTextAndType(cmd, pakCommandName);

            /*if (cmd.CommandText == string.Empty)
            {
                log.Debug(string.Format("不支持 {0} 命令！", pakCommandName));
                return effectedCount;
            }*/

            //SqlConnection nMGConn = null;
            //SqlCommand nMGCmd = null;
            GBaseConnection nMGConn = null;
            GBaseCommand nMGCmd = null;
            
            //CHECK ON:对应老版统计库，其他库看情况而定，最好取默认值
            if (string.Compare(pakDBName, "新统计库", true) == 0 && NMGCommand.CheckNMGPakUpdateCommand(pakCommandName))
            {
                //nMGConn = new SqlConnection(ConfigurationManager.ConnectionStrings["内蒙基础库"].ConnectionString);
                nMGConn = new GBaseConnection(ConfigurationManager.ConnectionStrings["内蒙基础库"].ConnectionString);
                nMGConn.Open();
                nMGCmd = nMGConn.CreateCommand();
                //nMGCmd.CommandType = CommandType.Text;
                nMGCmd.CommandTimeout = 0;

                NMGCommand.GetCommentTextAndType(nMGCmd, pakCommandName, Text);
            }

            if (_parameters != null && _parameters.Count > 0)
            {
                try
                {
                    cmd.CommandTimeout = 0;
                    while (_dataReader.Position < _dataReader.Length)
                    {
                        try
                        {
                            effectedCount += InternalExecute(cmd, nMGCmd, pakCommandName, ref nMGEffectedCount);
                        }
                        catch (Exception exp)
                        {
                            log.ErrorFormat("执行【{0}】失败，{1}", cmd.CommandText, exp.Message);
                            log.Debug(string.Format("执行【{0}】失败，{1}", cmd.CommandText, exp.Message), exp);
                            throw new Exception(string.Format("语句【{0}】，{1}", cmd.CommandText, exp.Message), exp); //TODO for debug
                        }
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (nMGConn != null)
                    {
                        nMGConn.Close();
                        nMGConn.Dispose();
                    }
                }
                
            }
            else
            {
                try
                {
                	cmd.CommandTimeout = 0;
                    //cmd.CommandText = Text;
                    effectedCount = cmd.ExecuteNonQuery();
                    if (effectedCount < 0)
                    {
                        effectedCount = 0;
                    }

                    if (nMGCmd != null)
                    {
                    		nMGCmd.CommandTimeout = 0;
                        nMGEffectedCount = nMGCmd.ExecuteNonQuery();
                        if (nMGEffectedCount < 0)
                        {
                            nMGEffectedCount = 0;
                        }
                    }
                }
                catch (Exception exp)
                {
                    log.ErrorFormat("执行【{0}】失败，{1}", cmd.CommandText, exp.Message);
                    log.Debug(string.Format("执行【{0}】失败，{1}", cmd.CommandText, exp.Message), exp);
                    throw new Exception(string.Format("语句【{0}】，{1}", cmd.CommandText, exp.Message), exp); //TODO for debug
                }
                finally
                {
                    if (nMGConn != null)
                    {
                        nMGConn.Close();
                        nMGConn.Dispose();
                    }
                }
            }

            if (nMGCmd != null)
            {
                log.InfoFormat("【内蒙基础库】：执行结束，来自解包命令【{0}】，影响行数【{1}】", pakCommandName, nMGEffectedCount);
            }
                        
            return effectedCount;
        }

        /// <summary>  
        /// 执行注册表导入  
        /// </summary>  
        /// <param name="regPath">注册表文件路径</param>  
        public void ExecuteReg(string regPath)
        {
            if (File.Exists(regPath))
            {
                regPath = @"""" + regPath + @"""";
                Process.Start("regedit", string.Format(" /s {0}", regPath));
            }
        }

        public void ExecuteExe(string para, string argu)
        {
            ProcessStartInfo psInfo = new ProcessStartInfo();
            psInfo.FileName = para;//需要启动的程序名 
            psInfo.WindowStyle = ProcessWindowStyle.Hidden;
            psInfo.Arguments = argu;//启动参数 

            Process p = null;
            try
            {
                p = new Process();
                p.StartInfo = psInfo;

                p.Start();//启动 

                if (p.HasExited)//判断是否运行结束 
                {
                    p.Kill();
                }
            }
            catch (Exception exp)
            {
                log.ErrorFormat("执行程序异常,{0}", exp.Message);
                log.Debug(string.Format("执行程序异常,{0}", exp.Message), exp);
            }
        }

        public long BulkExecute(IDbConnection cn, string sFileName)
        {
            long effectedCount = 0;

            if (!string.IsNullOrEmpty(sFileName))
            {
                IDbCommand cmd = cn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandTimeout = 0;
                cmd.CommandText = string.Format(ReserveText, sFileName);
                try
                {
                    effectedCount = cmd.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    log.ErrorFormat("执行【{0}】失败，{1}", cmd.CommandText, exp.Message);
                    log.Debug(string.Format("执行【{0}】失败，{1}", cmd.CommandText, exp.Message), exp);
                    throw new Exception(string.Format("语句【{0}】，{1}", cmd.CommandText, exp.Message), exp);
                }
                finally
                {
                    try
                    {
                        File.Delete(sFileName);
                    }
                    catch
                    {
                    }
                }
            }
            return effectedCount;
        }
        #endregion

        #region Internal Static Methods

        internal static void GetCommentTextAndType(IDbCommand command, string pakCommandName)
        {
            if (command == null || string.IsNullOrEmpty(pakCommandName))
            {
                return;
            }

            string cmdText = string.Empty;
            CommandType cType = CommandType.Text;

            if (string.Compare(pakCommandName, "MODIFY_tbArea", true) == 0)
            {
                cmdText = @"DELETE FROM tbArea WHERE fdCode=?fdCode OR fdName=?fdName;
                            INSERT INTO tbArea (fdCode,fdName,fdAlias,fdState,fdUpdateTime) VALUES(?fdCode,?fdName,?fdAlias,?fdState,?fdUpdateTime);";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbData_New", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbData_NewOrder", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbData_Old", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbData", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "MODIFY_tbData";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbSequence_New", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbSequence_NewOrder", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbSequence_Old", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbSequence", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "MODIFY_tbSequence";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbSequenceWithSequences", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "MODIFY_tbSequenceWithSequences";
            }
            else if (string.Compare(pakCommandName, "INITIALIZER_tbProductTree_TEMP", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "INITIALIZER_tbProductTree_TEMP";
            }
            else if (string.Compare(pakCommandName, "INITIALIZER_tbProductTree_TEMP_User", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "INITIALIZER_tbProductTree_TEMP_User";
            }
            else if (string.Compare(pakCommandName, "INSERT_INTO_Temp_FROM_tbProductTree", true) == 0)
            {
                cmdText = @"INSERT INTO tbProductTreeTemp(fdNodeID,fdSyncID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime) 
                            SELECT fdNodeID,2,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime FROM tbProductTree 
                            WHERE (fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%'));";
            }
            else if (string.Compare(pakCommandName, "INSERT_INTO_Temp_FROM_tbProductTree_User", true) == 0)
            {
                cmdText = @"INSERT INTO tbProductTreeTemp_User(fdNodeID,fdSyncID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime) 
                            SELECT fdNodeID,2,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime FROM tbProductTree 
                            WHERE (fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%'));";
            }
            else if (string.Compare(pakCommandName, "INSERT_tbProductTree_TEMP", true) == 0)
            {
                cmdText = @"INSERT INTO tbProductTreeTemp (fdNodeID,fdSyncID, fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime) 
                VALUES(?fdNodeID,1,?fdNodePath,?fdParentID,?fdType,?fdName,?fdXIndex,?fdUpdateTime)";
            }
            else if (string.Compare(pakCommandName, "INSERT_tbProductTree_TEMP_User", true) == 0)
            {
                cmdText = @"INSERT INTO tbProductTreeTemp_User (fdNodeID,fdSyncID, fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime) 
                VALUES(?fdNodeID,1,?fdNodePath,?fdParentID,?fdType,?fdName,?fdXIndex,?fdUpdateTime)";
            }
            else if (string.Compare(pakCommandName, "DELETE_tbProductTree", true) == 0)
            {
                cmdText = @"DELETE FROM tbProductTree WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%');";
            }
            else if (string.Compare(pakCommandName, "INSERT_INTO_tbProductTree_FROM_Temp", true) == 0)
            {
                cmdText = @"INSERT INTO tbProductTree(fdNodeID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime) 
                            SELECT fdNodeID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime FROM tbProductTreeTemp WHERE fdSyncID=1;";
            }
            else if (string.Compare(pakCommandName, "INSERT_INTO_tbProductTree_FROM_Temp_User", true) == 0)
            {
                cmdText = @"INSERT INTO tbProductTree(fdNodeID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime) 
                            SELECT fdNodeID,fdNodePath,fdParentID,fdType,fdName,fdXIndex,fdUpdateTime FROM tbProductTreeTemp_User WHERE fdSyncID=1;";
            }
            else if (string.Compare(pakCommandName, "DELETE_ProductTree_Structure", true) == 0)
            {
                cmdText = @"DELETE FROM tbDimensionValueForDimensions WHERE fdDimensionID IN 
                (
                  SELECT DWD.fdDimensionID FROM tbDimensionWithDimensions DWD 
                  INNER JOIN tbDimensionsWithSequences DWS ON DWD.fdDimensionsID=DWS.fdDimensionsID
                  WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                );
                 
              DELETE FROM tbDimensionForDimensions WHERE fdID IN 
                (SELECT DWD.fdDimensionID FROM tbDimensionWithDimensions DWD 
                INNER JOIN tbDimensionsWithSequences DWS ON DWD.fdDimensionsID=DWS.fdDimensionsID
                WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                ); 
                 
              DELETE FROM tbDimensionWithDimensions WHERE fdDimensionsID IN 
                (SELECT DWS.fdDimensionsID FROM tbDimensionsWithSequences DWS WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                );    
              
              DELETE FROM tbDimensions WHERE fdID IN 
                (SELECT DWS.fdDimensionsID FROM tbDimensionsWithSequences DWS WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                );        
                 
              DELETE FROM tbDimensionsWithSequences WHERE fdSequencesID IN 
                 ( 
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  );
                 
              DELETE FROM tbSequenceWithSequences WHERE fdSequencesID IN 
                 ( 
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  );
                 
              DELETE FROM tbSequences WHERE fdID IN 
                 ( 
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  );";

            }
            else if (string.Compare(pakCommandName, "DELETE_ProductTree_Structure_User", true) == 0)
            {
                cmdText = @"DELETE FROM tbDimensionValueForDimensions WHERE fdDimensionID IN 
                (
                  SELECT DWD.fdDimensionID FROM tbDimensionWithDimensions DWD 
                  INNER JOIN tbDimensionsWithSequences DWS ON DWD.fdDimensionsID=DWS.fdDimensionsID
                  WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                );
                 
              DELETE FROM tbDimensionForDimensions WHERE fdID IN 
                (SELECT DWD.fdDimensionID FROM tbDimensionWithDimensions DWD 
                INNER JOIN tbDimensionsWithSequences DWS ON DWD.fdDimensionsID=DWS.fdDimensionsID
                WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                ); 
                
              DELETE FROM tbDimensionWithDimensions WHERE fdDimensionsID IN 
                (SELECT DWS.fdDimensionsID FROM tbDimensionsWithSequences DWS WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                );    
              
              DELETE FROM tbDimensions WHERE fdID IN 
                (SELECT DWS.fdDimensionsID FROM tbDimensionsWithSequences DWS WHERE DWS.fdSequencesID IN
                  (
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  )
                );        
                 
              DELETE FROM tbDimensionsWithSequences WHERE fdSequencesID IN 
                 ( 
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  );
                 
              DELETE FROM tbSequenceWithSequences WHERE fdSequencesID IN 
                 ( 
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  );
                 
              DELETE FROM tbSequences WHERE fdID IN 
                 ( 
                    SELECT DISTINCT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=2 AND 
                    fdNodeID NOT IN
                    (
                      SELECT fdNodeID FROM tbProductTreeTemp_User WHERE fdNodePath LIKE CONCAT('%', CAST(?fdNodeID AS CHAR(64)), '%') AND fdType=1 AND fdSyncID=1
                    )
                  );";
            }
            else if (string.Compare(pakCommandName, "DROP_tbProductTreeTemp", true) == 0)
            {
                cmdText = @"DROP TABLE tbProductTreeTemp;";
            }
            else if (string.Compare(pakCommandName, "DROP_tbProductTreeTemp_User", true) == 0)
            {
                cmdText = @"DROP TABLE tbProductTreeTemp_User;";
            }
            else if (string.Compare(pakCommandName, "UPDATE_tbDataWithInvalidState", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "UPDATE_tbDataWithInvalidState";
            }
            else if (string.Compare(pakCommandName, "UPDATE_tbSequenceWithInvalidState", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "UPDATE_tbSequenceWithInvalidState";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbUnit", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "MODIFY_tbUnit";
            }
            else if (string.Compare(pakCommandName, "TRUNCATE_DBTABLES", true) == 0)
            {
                cmdText = @"TRUNCATE TABLE tbArea;
                 TRUNCATE TABLE tbData;
                 TRUNCATE TABLE tbUnit;
                 TRUNCATE TABLE tbCalculateSequence;
                 TRUNCATE TABLE tbDimensionForDimensions;
                 TRUNCATE TABLE tbDimensions;
                 TRUNCATE TABLE tbDimensionsWithSequences;
                 TRUNCATE TABLE tbDimensionValueForDimensions;
                 TRUNCATE TABLE tbDimensionWithDimensions;
                 TRUNCATE TABLE tbFrequency;
                 TRUNCATE TABLE tbProductTree;
                 TRUNCATE TABLE tbSequence;
                 TRUNCATE TABLE tbSequences;
                 TRUNCATE TABLE tbSequenceWithCalculateSequence;
                 TRUNCATE TABLE tbSequenceWithSequences;
                 TRUNCATE TABLE tbDocument;
                 TRUNCATE TABLE tbColourConfig;
                 TRUNCATE TABLE tbColourScheme;
                 TRUNCATE TABLE tbProductInfo;";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbProductTree", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = "MODIFY_tbProductTree";
            }
            else if (string.Compare(pakCommandName, "UPDATE_tbSequencesfdNewDataUpdateTime", true) == 0)
            {
                cmdText = @"UPDATE tbSequences SET 
                            fdNewDataUpdateTime=IFNULL((SELECT MAX(S.fdNewDataUpdateTime) 
                            FROM tbSequence S INNER JOIN tbSequenceWithSequences SWS ON S.fdID=SWS.fdSequenceID 
							WHERE SWS.fdSequencesID=tbSequences.fdID),'1900-1-1') 
                            WHERE fdID IN ( SELECT fdNodeID FROM tbProductTree 
                            WHERE (fdNodePath LIKE CONCAT('%', CAST(?ProductNodeID AS CHAR(64)), '%')) AND fdType=1
                        )";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbSequences", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbSequences";
            }
            else if (string.Compare(pakCommandName, "UPDATE_tbSequencesfdNewDataUpdateTime_FROM_DBMiddle", true) == 0)
            {
                cmdText = @"UPDATE tbSequences SET 
		            fdNewDataUpdateTime=IFNULL((SELECT MAX(S.fdNewDataUpdateTime) 
		            FROM tbSequence S INNER JOIN tbSequenceWithSequences SWS ON S.fdID=SWS.fdSequenceID WHERE SWS.fdSequencesID=tbSequences.fdID),'1900-1-1') 
		            WHERE fdID IN (SELECT fdSequencesID FROM tbSequenceWithSequences WHERE fdSequenceID =?fdID)";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbDimensionForDimensions", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbDimensionForDimensions_FULL", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbDimensionForDimensions";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbDimensionValueForDimensions", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbDimensionValueForDimensions_FULL", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbDimensionValueForDimensions";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbDimensions", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbDimensions_FULL", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbDimensions";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbDimensionWithDimensions", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbDimensionWithDimensions_FULL", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbDimensionWithDimensions";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbDimensionsWithSequences", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbDimensionsWithSequences";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbSequenceForCalculateSequence", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbSequenceForCalculateSequence";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbDocument", true) == 0 ||
                string.Compare(pakCommandName, "MODIFY_tbDocumentStatic_Full", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbDocument";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbCalculateSequence", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbCalculateSequence";
            }
            else if (string.Compare(pakCommandName, "DELETE_tbSequenceWithCalculateSequence", true) == 0)
            {
                cmdText = @"DELETE FROM tbSequenceWithCalculateSequence WHERE fdCalSequenceID =?fdCalSequenceID";
            }
            else if (string.Compare(pakCommandName, "INSERT_tbSequenceWithCalculateSequence", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"INSERT_tbSequenceWithCalculateSequence";
            }
            else if (string.Compare(pakCommandName, "INSERT_tbColourScheme", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"INSERT_tbColourScheme";
            }
            else if (string.Compare(pakCommandName, "DELETE_tbColourScheme", true) == 0)
            {
                cmdText = @"DELETE FROM tbColourScheme WHERE fdSchemeID=?fdSchemeID;
                    DELETE FROM tbColourConfig WHERE fdSchemeID=?fdSchemeID;";
            }
            else if (string.Compare(pakCommandName, "MODIFY_tbColourConfig", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_tbColourConfig";
            }
            else if (string.Compare(pakCommandName, "DELETE_ALL_ORDER_tbProductInfo", true) == 0)
            {
                cmdText = @"DELETE FROM tbProductInfo WHERE fdProductID=?fdProductID";
            }
            else if (string.Compare(pakCommandName, "MODIFY_ALL_ORDER_tbProductInfo", true) == 0)
            {
                cType = CommandType.StoredProcedure;
                cmdText = @"MODIFY_ALL_ORDER_tbProductInfo";
            }
            command.CommandText = cmdText;
            command.CommandType = cType;
        }


        /// <summary>
        /// 是否为固定长度类型
        /// </summary>
        /// <param name="sTypeName"></param>
        /// <returns></returns>
        internal static bool IsFixedLengthType(string sTypeName)
        {
            switch (sTypeName.Trim().ToLower())
            {
                case "bool":
                case "int":
                case "uint":
                case "long":
                case "ulong":
                case "short":
                case "ushort":
                case "float":
                case "double":
                case "char":
                case "decimal":
                case "byte":
                case "datetime":
                case "guid":
                    return true;
                default:
                    return false;
            }
        }

        /// <summary>
        /// 获取给定类型字节数
        /// </summary>
        /// <param name="sTypeName"></param>
        /// <returns></returns>
        internal static uint GetSizeOfType(string sTypeName)
        {
            uint iLength = 0;
            switch (sTypeName.Trim().ToLower())
            {
                case "bool":
                    iLength = sizeof(bool);
                    break;
                case "int":
                    iLength = sizeof(int);
                    break;
                case "uint":
                    iLength = sizeof(uint);
                    break;
                case "long":
                    iLength = sizeof(long);
                    break;
                case "ulong":
                    iLength = sizeof(ulong);
                    break;
                case "short":
                    iLength = sizeof(short);
                    break;
                case "ushort":
                    iLength = sizeof(ushort);
                    break;
                case "float":
                    iLength = sizeof(float);
                    break;
                case "double":
                    iLength = sizeof(double);
                    break;
                case "char":
                    iLength = sizeof(char);
                    break;
                case "decimal":
                    iLength = sizeof(decimal);
                    break;
                case "byte":
                    iLength = sizeof(byte);
                    break;
                case "datetime":
                    iLength = sizeof(Int64);
                    break;
                case "guid":
                    iLength = 16;
                    break;
                case "string":
                case "byte[]":
                    iLength = sizeof(uint);
                    break;
                default:
                    iLength = sizeof(uint);
                    break;
            }
            return iLength;
        }

        /// <summary>
        /// 把二进制数据转换为给定类型数据
        /// </summary>
        /// <param name="buffer"></param>
        /// <param name="sTypeName"></param>
        /// <returns></returns>
        internal static object ConvertBytesToObj(byte[] buffer, string sTypeName)
        {
            object objValue = buffer;
            switch (sTypeName.Trim().ToLower())
            {
                case "bool":
                    objValue = BitConverter.ToBoolean(buffer, 0);
                    break;
                case "int":
                    objValue = BitConverter.ToInt32(buffer, 0);
                    break;
                case "uint":
                    objValue = BitConverter.ToUInt32(buffer, 0);
                    break;
                case "long":
                    objValue = BitConverter.ToInt64(buffer, 0);
                    break;
                case "ulong":
                    objValue = BitConverter.ToUInt64(buffer, 0);
                    break;
                case "short":
                    objValue = BitConverter.ToInt16(buffer, 0);
                    break;
                case "ushort":
                    objValue = BitConverter.ToUInt16(buffer, 0);
                    break;
                case "float":
                    objValue = BitConverter.ToSingle(buffer, 0);
                    break;
                case "double":
                    objValue = BitConverter.ToDouble(buffer, 0);
                    break;
                case "char":
                    objValue = BitConverter.ToChar(buffer, 0);
                    break;
                case "decimal":
                    int[] iBits = new int[4];
                    for (int i = 0; i < 4; i++)
                    {
                        byte[] tempBuffer = new byte[4];
                        Array.Copy(buffer, i * 4, tempBuffer, 0, 4);
                        iBits[i] = BitConverter.ToInt32(tempBuffer, 0);
                    }
                    objValue = new decimal(iBits);
                    break;
                case "byte":
                    objValue = buffer[0];
                    break;
                case "datetime":
                    objValue = new DateTime(BitConverter.ToInt64(buffer, 0));
                    break;
                case "guid":
                    objValue = new Guid(buffer);
                    break;
                case "string":
                    objValue = System.Text.Encoding.UTF8.GetString(buffer);
                    break;
            }
            return objValue;
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// 执行一行数据
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        //private int InternalExecute(IDbCommand cmd, SqlCommand nMGCmd,string pakCommandName, ref long nMGEffectedCount)
        //{
        private int InternalExecute(IDbCommand cmd,GBaseCommand nMGCmd, string pakCommandName, ref long nMGEffectedCount)
        {
            string strCmdText = Text;
            cmd.Parameters.Clear();

            if (nMGCmd != null)
            {
                nMGCmd.Parameters.Clear();
            }

            int effectedCount = 0;
            for (int i = 0; i < _parameters.Count; i++)
            {
                ParameterInfo pInfo = _parameters[i];
                IDataParameter para = cmd.CreateParameter();
                if (cmd.CommandType == CommandType.StoredProcedure)
                {
                    para.ParameterName = string.Format("g_{0}", pInfo.Name);
                }
                else
                {
                    para.ParameterName = string.Format("?{0}", pInfo.Name);
                }

                try
                {                    
                    para.DbType = ConvertDbType(pInfo.Type);
                    CommandValue cValue = _commandValues[i];

                    byte[] bMark = null;
                    //判断是否允许为空
                    if (cValue.AllowNull)
                    {
                        bMark = new byte[1];
                        _dataReader.Read(bMark, 0, 1);

                        //判断是否为空

                        if (bMark[0] == 1)
                        {
                            para.Value = DBNull.Value;
                        }
                        else
                        {
                            uint iLength = 0;
                            if (IsFixedLengthType(cValue.Type))
                            {
                                iLength = GetSizeOfType(cValue.Type);
                            }
                            else
                            {
                                if (cValue.Length == 0)
                                {
                                    bMark = new byte[GetSizeOfType(cValue.Type)];
                                    _dataReader.Read(bMark, 0, bMark.Length);
                                    iLength = BitConverter.ToUInt32(bMark, 0);

                                }
                                else
                                {
                                    iLength = cValue.Length;
                                }
                            }

                            byte[] bValue = new byte[iLength];
                            _dataReader.Read(bValue, 0, bValue.Length);

                            para.Value = ConvertBytesToObj(bValue, cValue.Type);
                        }
                    }
                    else
                    {
                        uint iLength = 0;

                        if (IsFixedLengthType(cValue.Type))
                        {
                            iLength = GetSizeOfType(cValue.Type);
                        }
                        else
                        {
                            if (cValue.Length == 0)
                            {
                                bMark = new byte[GetSizeOfType(cValue.Type)];
                                _dataReader.Read(bMark, 0, bMark.Length);
                                iLength = BitConverter.ToUInt32(bMark, 0);
                            }
                            else
                            {
                                iLength = cValue.Length;
                            }
                        }

                        byte[] bValue = new byte[iLength];
                        _dataReader.Read(bValue, 0, bValue.Length);

                        para.Value = ConvertBytesToObj(bValue, cValue.Type);
                    }

                    if (para.DbType == DbType.DateTime)
                    {
                        if (para.Value != DBNull.Value && (DateTime)para.Value < DateTime.Parse("1753-1-1 12:00:00"))
                        {
                            DateTime dtTime = DateTime.MinValue;
                            try
                            {
                                dtTime = (DateTime)para.Value;
                            }
                            catch { }

                            if (dtTime != DateTime.MinValue && dtTime < DateTime.Parse("1753-1-1 12:00:00"))
                            {
                                para.DbType = DbType.String;
                                para.Value = ((DateTime)para.Value).ToString();
                            }
                        }
                    }
                    cmd.Parameters.Add(para);
                    if (nMGCmd != null)
                    {
                        nMGCmd.Parameters.AddWithValue(para.ParameterName, para.Value);
                    }
                    
                    if (pInfo.RefIndex < 0)
                    {
                        strCmdText = strCmdText.Replace(string.Format("&{0}&", pInfo.Name), para.Value.ToString());
                    }
                }
                catch(Exception exp)
                {
                    log.Error(string.Format("读取命令参数[{0}]出错，{1}{2}", para.ParameterName, exp.Message, strCmdText), exp);
                    throw;
                }
            }
            if (string.Compare(strCmdText.Trim(), NewCEDBRegModel.FullMark, true) == 0 ||
                string.Compare(strCmdText.Trim(), NewCEDBRegModel.IncrementMark, true) == 0)
            {
                NewCEDBRegModel regModel = new NewCEDBRegModel(Text.Trim());
                regModel.InitializeRegPath(cmd.Parameters);
                regModel.ExecuteReg();
            }
            else
            {
                bool reserverExe = false;
                try
                {
                    //strCmdText = strCmdText.Replace("@", "?");
                    //cmd.CommandText = strCmdText;
                    if (cmd.CommandText != string.Empty)
                    {
                        effectedCount = cmd.ExecuteNonQuery();
                    }

                    if (effectedCount < 0)
                    {
                        effectedCount = 0;
                    }
                   
                    if (effectedCount == 0 && !string.IsNullOrEmpty(ReserveText) && ReserveText.Trim().Length > 0)
                    {
                        reserverExe = true;
                        try
                        {
                            cmd.CommandText = ReserveText;
                            effectedCount = cmd.ExecuteNonQuery();
                            if (effectedCount < 0)
                            {
                                effectedCount = 0;
                            }
                            log.Debug("替代语句执行成功。");
                        }
                        catch (Exception exp)
                        {
                            log.Error(string.Format("执行替代语句失败，{0}", exp.Message), exp);
                            throw new Exception(string.Format("语句【{0}】，{1}", ReserveText, exp.Message), exp);
                        }
                    }
                }
                catch (DbException dbExp)
                {
                    if (reserverExe)
                    {
                        throw;
                    }

                    //log.Debug("执行失败，执行替代语句。"); 
                    if (effectedCount == 0 && !string.IsNullOrEmpty(ReserveText) && ReserveText.Trim().Length > 0)
                    {
                        try
                        {
                            cmd.CommandText = ReserveText;
                            effectedCount = cmd.ExecuteNonQuery();
                            if (effectedCount < 0)
                            {
                                effectedCount = 0;
                            }
                            log.Debug("替代语句执行成功。");
                        }
                        catch (Exception exp)
                        {
                            log.ErrorFormat("执行替代语句失败，{0}", exp.Message);
                            log.Debug(string.Format("执行替代语句失败，{0}", exp.Message), exp);
                            throw new Exception(string.Format("语句【{0}】，{1}", ReserveText, exp.Message), exp);
                        }
                    }
                    else
                    {
                        throw new Exception(string.Format("语句【{0}】，{1}", strCmdText, dbExp.Message), dbExp);
                    }
                }
            }

            if (nMGCmd != null)
            {
                try
                {
                    nMGEffectedCount +=nMGCmd.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    log.Error(string.Format("【内蒙基础库】：执行语句【{0}】失败，{1}", nMGCmd.CommandText, exp.Message), exp);
                    throw new Exception(string.Format("【内蒙基础库】：执行语句【{0}】，{1}", nMGCmd.CommandText, exp.Message), exp);
                }
            }
            
            return effectedCount;
        }

        private DbType ConvertDbType(DbType dbType)
        {
            if (ProviderName.Contains("SqlClient"))
            {
                switch (dbType)
                {
                    case DbType.UInt16:
                        return DbType.Int16;
                    case DbType.UInt32:
                        return DbType.Int32;
                    case DbType.UInt64:
                        return DbType.Int64;
                    case DbType.Date:
                        return DbType.DateTime;
                    case DbType.SByte:
                        return DbType.Byte;
                    case DbType.Time:
                        return DbType.DateTime;
                    case DbType.VarNumeric:
                        return DbType.Decimal;
                    default:
                        return dbType;
                }

            }
            else
            {
                return dbType;
            }
        }

        #endregion
    }

    /// <summary>
    /// 特例，执行CEDB客户端注册表的修正。
    /// </summary>
    public class NewCEDBRegModel
    {
        public const string FullMark = "REGEDIT__FULL";

        public const string IncrementMark = "REGEDIT_INCREMENT";

        private RegistryKey scorpioReg = null;

        private RegistryKey subDbReg = null;

        private string _regMark;

        private IDataParameterCollection _paras = null;

        public NewCEDBRegModel(string mark)
        {
            _regMark = mark;
        }

        public void InitializeRegPath(IDataParameterCollection paras)
        {
            _paras = paras;
            RegistryKey localReg = Registry.LocalMachine;
            scorpioReg = localReg.OpenSubKey(@"SOFTWARE\CEInet\Scorpio", true);
            if (scorpioReg == null)
            {
                scorpioReg = localReg.CreateSubKey(@"SOFTWARE\CEInet\Scorpio");
            }

            foreach (IDataParameter para in paras)
            {
                if (string.Compare("@Alias", para.ParameterName, true) == 0)
                {
                    subDbReg = scorpioReg.OpenSubKey(string.Format(@"Database\{0}", para.Value.ToString()), true);
                    if (subDbReg == null)
                    {
                        subDbReg = scorpioReg.CreateSubKey(string.Format(@"Database\{0}", para.Value.ToString()));
                    }

                    break;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strName"></param>
        /// <param name="strValue"></param>
        public void ExecuteReg()
        {
            if (string.Compare(_regMark, FullMark, true) == 0)
            {
                object serverIP = scorpioReg.GetValue("ServerIP");
                if (serverIP == null)
                {
                    scorpioReg.SetValue("ServerIP", "localhost");
                }

                object databaseName = scorpioReg.GetValue("DatabaseName");
                if (databaseName == null)
                {
                    scorpioReg.SetValue("DatabaseName", "CEDB");
                }
                object IsHide = subDbReg.GetValue("IsHide");
                if (IsHide == null)
                {
                    subDbReg.SetValue("IsHide", "0");
                }

                object DBAlias = subDbReg.GetValue("DBAlias");
                if (DBAlias == null)
                {
                    subDbReg.SetValue("DBAlias", ((IDataParameter)_paras["@Alias"]).Value.ToString());
                }
                object DBUID = subDbReg.GetValue("DBUID");
                if (DBUID == null)
                {
                    subDbReg.SetValue("DBUID", ((IDataParameter)_paras["@Alias"]).Value.ToString());
                }
                object DBPWD = subDbReg.GetValue("DBPWD");
                if (DBPWD == null)
                {
                    subDbReg.SetValue("DBPWD", ((IDataParameter)_paras["@Alias"]).Value.ToString());
                }

                switch (((IDataParameter)_paras["@Name"]).Value.ToString())
                {
                    case "Name":
                        object DBCName = subDbReg.GetValue("DBCName");
                        if (DBCName == null)
                        {
                            subDbReg.SetValue("DBCName", ((IDataParameter)_paras["@Value"]).Value.ToString());
                        }
                        break;
                    case "XOrder":
                        object DBXOrder = subDbReg.GetValue("DBXOrder");
                        if (DBXOrder == null)
                        {
                            subDbReg.SetValue("DBXOrder", ((IDataParameter)_paras["@Value"]).Value.ToString());
                        }
                        break;
                    case "DefaultIndicatorNames":
                        object DefaultIndicators = subDbReg.GetValue("DefaultIndicators");
                        if (DefaultIndicators == null)
                        {
                            subDbReg.SetValue("DefaultIndicators", ((IDataParameter)_paras["@Value"]).Value.ToString());
                        }
                        break;
                    case "DefaultAreaNames":
                        object DefaultAreas = subDbReg.GetValue("DefaultAreas");
                        if (DefaultAreas == null)
                        {
                            subDbReg.SetValue("DefaultAreas", ((IDataParameter)_paras["@Value"]).Value.ToString());
                        }
                        break;
                    case "DefaultFrequency":
                        object DefaultFrequency = subDbReg.GetValue("DefaultFrequency");
                        if (DefaultFrequency == null)
                        {
                            subDbReg.SetValue("DefaultFrequency", ((IDataParameter)_paras["@Value"]).Value.ToString());
                        }
                        break;
                    case "DefaultDateRange":
                        object DefaultDateRange = subDbReg.GetValue("DefaultDateRange");
                        if (DefaultDateRange == null)
                        {
                            subDbReg.SetValue("DefaultDateRange", ((IDataParameter)_paras["@Value"]).Value.ToString());
                        }
                        break;

                    default:
                        break;
                }
            }
            else if (string.Compare(_regMark, IncrementMark, true) == 0)
            {
                object UpdateTime = subDbReg.GetValue("UpdateTime");
                subDbReg.SetValue("UpdateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            }
        }
    }
}
